﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLite.NET;
using System.Diagnostics;
using System.Windows.Forms;
using MediaPortal.Configuration;

namespace BierdopjeTV.Database
{
    static class createDatabase
    {
        static public void CheckIfTablesExist()
        {
            string _Query;
            try
            {
                SQLiteClient b = new SQLiteClient(Config.GetFile(Config.Dir.Database, "BierdopjeTV.db3").ToString());

                SQLiteResultSet results;

                results = b.Execute("SELECT * FROM sqlite_master WHERE name='BierdopjeShows'");
                if (results.Rows.Count == 0)
                {
                
                    // table BierdopjeShows
                    _Query = "";
                    _Query += "CREATE TABLE BierdopjeShows" + Environment.NewLine;
                    _Query += "(" + Environment.NewLine;
                    _Query += "	ShowID INTEGER PRIMARY KEY," + Environment.NewLine;
                    _Query += "	TVDBID NUMERIC," + Environment.NewLine;
                    _Query += "	ShowName TEXT," + Environment.NewLine;
                    _Query += "	Seasons NUMERIC," + Environment.NewLine;
                    _Query += "	Episodes NUMERIC," + Environment.NewLine;
                    _Query += "	FirstAired TEXT," + Environment.NewLine;
                    _Query += "	LastAired TEXT," + Environment.NewLine;
                    _Query += "	NextEpisode TEXT," + Environment.NewLine;
                    _Query += "	RunTime NUMERIC," + Environment.NewLine;
                    _Query += "	HasTranslators TEXT," + Environment.NewLine;
                    _Query += "	ShowStatus TEXT," + Environment.NewLine;
                    _Query += "	Favorites NUMERIC," + Environment.NewLine;                    
                    _Query += "	Score Numeric," + Environment.NewLine;
                    _Query += "	Network TEXT," + Environment.NewLine;
                    _Query += "	AirTime TEXT," + Environment.NewLine;
                    _Query += "	Summary TEXT" + Environment.NewLine;
                    _Query += ")" + Environment.NewLine;

                    b.Execute(_Query);
                }
                results = null;

                // table BierdopjeEpisodes
                results = b.Execute("SELECT * FROM sqlite_master WHERE name='BierdopjeEpisodes'");
                if (results.Rows.Count == 0)
                {
                    _Query = "";
                    _Query += "CREATE TABLE BierdopjeEpisodes" + Environment.NewLine;
                    _Query += "( " + Environment.NewLine;
                    _Query += "	EpisodeID INTEGER PRIMARY KEY," + Environment.NewLine;
                    _Query += "	ShowID NUMERIC," + Environment.NewLine;
                    _Query += "	Titel TEXT," + Environment.NewLine;
                    _Query += "	Season NUMERIC," + Environment.NewLine;
                    _Query += "	Episode NUMERIC," + Environment.NewLine;
                    _Query += "	AirDate TEXT," + Environment.NewLine;
                    _Query += "	TVDBID NUMERIC, " + Environment.NewLine;
                    _Query += "	Votes NUMERIC, " + Environment.NewLine;
                    _Query += "	Score Numeric, " + Environment.NewLine;
                    _Query += "	SubsNL TEXT, " + Environment.NewLine;
                    _Query += "	SubsEN TEXT," + Environment.NewLine;
                    _Query += "	Downloaded TEXT," + Environment.NewLine;
                    _Query += "	Watched TEXT," + Environment.NewLine;
                    _Query += "	SubNLDownloaded TEXT," + Environment.NewLine;
                    _Query += "	SubENDownloaded TEXT," + Environment.NewLine;
                    _Query += "	Custom1 TEXT," + Environment.NewLine;
                    _Query += "	Custom2 TEXT," + Environment.NewLine;
                    _Query += "	Custom3 TEXT," + Environment.NewLine;
                    _Query += "	Custom4 TEXT," + Environment.NewLine;

                    _Query += "	Summary TEXT " + Environment.NewLine;
                    _Query += ")" + Environment.NewLine;
                    _Query += "" + Environment.NewLine;

                    b.Execute(_Query);

                }
                results = null;

                results = b.Execute("SELECT * FROM sqlite_master WHERE name='Episodes'");
                if (results.Rows.Count == 0)
                {
                    //table Episodes (Means local)
                    b.Execute("CREATE TABLE Episodes " +
                                 "(" +
                                 "   EpisodeID INTEGER PRIMARY KEY, ShowID NUMERIC, Season NUMERIC, Episode NUMERIC," +
                                 "   Source TEXT, Codec TEXT, Path TEXT" +
                                 ")"
                                );
                }
                results = null;


                results = b.Execute("SELECT * FROM sqlite_master WHERE name='ReplaceMents'");
                if (results.Rows.Count == 0)
                {
                    //table ReplaceMents 
                    b.Execute("CREATE TABLE ReplaceMents" +
                              "(" +
                              "   ReplaceMentID INTEGER PRIMARY KEY, ShowNameOld Text, ShowNameNew TEXT" +
                              ")"
                             );
                }

                results = null;

                results = b.Execute("SELECT * FROM sqlite_master WHERE name='ImportPaths'");
                if (results.Rows.Count == 0)
                {
                    //table ReplaceMents 
                    b.Execute("CREATE TABLE ImportPaths" +
                              "(" +
                              "   ImportPathID INTEGER PRIMARY KEY, ImportPath Text, Enabled TEXT" +
                              ")"
                             );
                }

                results = null;

                //table Settings
                results = b.Execute("SELECT * FROM sqlite_master WHERE name='Settings'");
                if (results.Rows.Count == 0)
                {

                    b.Execute("CREATE TABLE Settings" +
                              "(" +
                              "   SettingID INTEGER PRIMARY KEY, SettingName Text, SettingValue TEXT" +
                              ")"
                             );
                    //Default values
                    b.Execute("INSERT INTO Settings(SettingName, SettingValue) VALUES ('AchtervoegselNLSub', 'NL')");
                    b.Execute("INSERT INTO Settings(SettingName, SettingValue) VALUES ('AchtervoegselENSub', 'EN')");
                    b.Execute("INSERT INTO Settings(SettingName, SettingValue) VALUES ('AlleenLokaleEpisodes', 'True')");
                    b.Execute("INSERT INTO Settings(SettingName, SettingValue) VALUES ('EpisodeGezien', '90')");
                    b.Execute("INSERT INTO Settings(SettingName, SettingValue) VALUES ('APIKey', '')");
                }
                results = null;


                //Table Genres

                results = b.Execute("SELECT * FROM sqlite_master WHERE name='Genres'");
                if (results.Rows.Count == 0)
                {
                    _Query = "";
                    _Query += "CREATE TABLE Genres" + Environment.NewLine;
                    _Query += "(" + Environment.NewLine;
                    _Query += "	GenreID INTEGER PRIMARY KEY," + Environment.NewLine;
                    _Query += "	Genre TEXT" + Environment.NewLine;
                    _Query += ")" + Environment.NewLine;
                    b.Execute(_Query);                
                }
                results = null;


                //table ShowGenres
                results = b.Execute("SELECT * FROM sqlite_master WHERE name='ShowGenres'");
                if (results.Rows.Count == 0)
                {
                    _Query = "";
                    _Query += "CREATE TABLE ShowGenres" + Environment.NewLine;
                    _Query += "(" + Environment.NewLine;
                    _Query += "	ShowGenreID INTEGER PRIMARY KEY," + Environment.NewLine;
                    _Query += "	GenreID NUMERIC," + Environment.NewLine;
                    _Query += "	ShowID NUMERIC" + Environment.NewLine;
                    _Query += ")" + Environment.NewLine;
                    b.Execute(_Query);
                }
                results = null;
              
                //view EpisodesMatched
                results = b.Execute("SELECT * FROM sqlite_master WHERE name='LocalEpisodesMatched'");
                if (results.Rows.Count == 0)
                {
                    _Query = "CREATE VIEW LocalEpisodesMatched AS" + Environment.NewLine;
                    _Query += "SELECT" + Environment.NewLine;
                    _Query += "	Episodes.ShowID," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.EpisodeID," + Environment.NewLine;
                    _Query += "	BierdopjeShows.ShowName," + Environment.NewLine;
                    _Query += "	Episodes.Season," + Environment.NewLine;
                    _Query += "	Episodes.Episode," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.Titel" + Environment.NewLine;
                    _Query += "" + Environment.NewLine;
                    _Query += "FROM" + Environment.NewLine;
                    _Query += "	Episodes" + Environment.NewLine;
                    _Query += "LEFT JOIN" + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes ON" + Environment.NewLine;
                    _Query += "	(" + Environment.NewLine;
                    _Query += "		BierdopjeEpisodes.ShowID = Episodes.ShowID" + Environment.NewLine;
                    _Query += "		AND" + Environment.NewLine;
                    _Query += "		BierdopjeEpisodes.Season = Episodes.Season" + Environment.NewLine;
                    _Query += "		AND" + Environment.NewLine;
                    _Query += "		BierdopjeEpisodes.Episode = Episodes.Episode" + Environment.NewLine;
                    _Query += "	)" + Environment.NewLine;
                    _Query += "LEFT JOIN" + Environment.NewLine;
                    _Query += "	BierdopjeShows ON BierdopjeShows.ShowID = Episodes.ShowID" + Environment.NewLine;

                    b.Execute(_Query);

                }
                results = null;








                //view LocalSeasons
                results = b.Execute("SELECT * FROM sqlite_master WHERE name='LocalSeasons'");
                if (results.Rows.Count == 0)
                {
                    _Query = "CREATE VIEW LocalSeasons AS" + Environment.NewLine;
                    _Query += "SELECT" + Environment.NewLine;
                    _Query += "	DISTINCT A.Season," + Environment.NewLine;
                    _Query += "	A.ShowID," + Environment.NewLine;
                    _Query += "	(SELECT Count(EpisodeID) FROM LocalEpisodesMatched WHERE (LocalEpisodesMatched.ShowID = A.ShowId AND LocalEpisodesMatched.Season = A.Season)) As AantalEpisodes," + Environment.NewLine;
                    _Query += "	(SELECT Count(Watched) FROM BierdopjeEpisodes AS B WHERE (B.ShowID = A.ShowID AND B.Season = A.Season)) As AantalGezien," + Environment.NewLine;
                    _Query += "	(SELECT Count(SubNLDownloaded) FROM BierdopjeEpisodes AS B WHERE (B.ShowID = A.ShowID AND B.Season = A.Season AND B.SubNLDownloaded = 1)) As SubsNL," + Environment.NewLine;
                    _Query += "	(SELECT Count(SubENDownloaded) FROM BierdopjeEpisodes AS B WHERE (B.ShowID = A.ShowID AND B.Season = A.Season AND B.SubENDownloaded = 1)) As SubsEN" + Environment.NewLine;
                    _Query += "FROM" + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes AS A" + Environment.NewLine;
                    b.Execute(_Query);

                }
                results = null;


                //View LocalEpisodes
                results = b.Execute("SELECT * FROM sqlite_master WHERE name='LocalEpisodes'");
                if (results.Rows.Count == 0)
                {
                    _Query = "CREATE VIEW LocalEpisodes AS" + Environment.NewLine;
                    _Query += "SELECT" + Environment.NewLine;
                    _Query += "	Episodes.Source," + Environment.NewLine;
                    _Query += "	Episodes.Codec," + Environment.NewLine;
                    _Query += "	Episodes.Path," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.ShowID," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.EpisodeID," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.Titel," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.Season," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.Episode," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.AirDate," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.Votes," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.Score," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.Watched," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.SubNLDownloaded AS SubNL," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.SubENDownloaded AS SubEN," + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes.Summary" + Environment.NewLine;
                    _Query += "FROM" + Environment.NewLine;
                    _Query += "	Episodes" + Environment.NewLine;
                    _Query += "LEFT JOIN" + Environment.NewLine;
                    _Query += "	BierdopjeEpisodes" + Environment.NewLine;
                    _Query += "	ON" + Environment.NewLine;
                    _Query += "	(" + Environment.NewLine;
                    _Query += "		Episodes.ShowID = BierdopjeEpisodes.ShowID" + Environment.NewLine;
                    _Query += "		AND" + Environment.NewLine;
                    _Query += "		Episodes.Season = BierdopjeEpisodes.Season" + Environment.NewLine;
                    _Query += "		AND" + Environment.NewLine;
                    _Query += "		Episodes.Episode = BierdopjeEpisodes.Episode" + Environment.NewLine;
                    _Query += "	)" + Environment.NewLine;                    
                    b.Execute(_Query);

                }
                results = null;


  
           

                b.Close();
            }
            catch (SQLiteException ex)
            {
                Debug.Print(ex.Message);
            }
        }
    }

}
